﻿using Dapper;
using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace QRCodeBluetooth.DAL
{
    public class CommFN<T>
    {
        public bool UpdateFN(T t, string Sql)
        {
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    int count = 0;
                    // if(trans==null)
                    count = conn.Execute(Sql.ToString(), t);
                    // else
                    //     conn.Execute(Sql.ToString(), t,trans);
                    return count > 0;
                }
                catch (Exception e)
                {
                    throw e;
                }

            }
        }
        public int UpdateFNReturnkey(T t, string Sql)
        {
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    int count = conn.ExecuteScalar<int>(Sql.ToString(), t);
                    return count;
                }
                catch (Exception e)
                {
                    throw e;
                }

            }
        }
        public bool UpdateFN(string Sql)
        {
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    int count = conn.Execute(Sql.ToString());
                    return count > 0;
                }
                catch (Exception e)
                {
                    return false;
                }
            }
        }
        public int GetPageRecords(string sql)
        {
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    int count = conn.ExecuteScalar<int>(sql);
                    return count;
                }
                catch (Exception e)
                {
                    return 0;
                }
            }
        }
        public T GetData(string Sql)
        {
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    T t = conn.QueryFirstOrDefault<T>(Sql.ToString());
                    return t;
                }
                catch (Exception e)
                {
                    return default(T);
                }
            }
        }
        public T1 GetDataDt<T1>(string Sql)
        {
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    T1 t = conn.QueryFirstOrDefault<T1>(Sql.ToString());
                    return t;
                }
                catch (Exception e)
                {
                    return default(T1);
                }
            }
        }
        public List<T> GetListData(string Sql)
        {
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    var t = conn.Query<T>(Sql.ToString()).AsList();
                    return t.AsList<T>();
                }
                catch (Exception e)
                {
                    return null;
                }
            }
        }
        public List<T1> GetListDataDt<T1>(string Sql)
        {
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    var t = conn.Query<T1>(Sql.ToString()).AsList();
                    return t.AsList<T1>();
                }
                catch (Exception e)
                {
                    return null;
                }
            }
        }
        public string GenarateSqlStr(T t, string PreName = "", bool matching = false)
        {
            Type type = typeof(T);
            PropertyInfo[] infos = type.GetProperties();
            StringBuilder sb = new StringBuilder();
            foreach (PropertyInfo info in infos)
            {
                object value = info.GetValue(t, null);
                if (value != null && !string.IsNullOrEmpty(value.ToString()))
                {
                    string name = PreName + info.Name;
                    if (info.PropertyType.ToString().Contains("DateTime"))
                    {
                        if (DateTime.Parse(value.ToString()).Year == 1)
                            continue;
                        if (name.Contains("BegDate"))
                        {
                            sb.Append(" BillDate >= '" + value + "' and ");
                        }
                        else if (name.Contains("EndDate"))
                        {
                            sb.Append(" BillDate <= '" + value + "' and ");
                        }
                        else
                        {
                            sb.Append(name + "='" + value + "' and ");
                        }
                    }
                    else if (info.PropertyType.Name.Contains("String"))
                    {
                        if (matching)
                            sb.Append(name + " = '" + value + "' and ");
                        else
                        {
                            sb.Append(name + " like '%" + value + "%' and ");
                        }
                    }
                    else if (info.PropertyType.Name.Contains("Nullable"))
                    {
                        if (info.PropertyType.ToString().Contains("Int") && int.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Decimal") && decimal.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Boolean"))
                        {

                            if (value.ToString().ToLower().Contains("false"))
                            {
                                value = "0";
                            }
                            else if (value.ToString().ToLower().Contains("true"))
                            {
                                value = "1";
                            }
                            else continue;
                        }
                        //    continue;
                        sb.Append(name + "=" + value + " and ");
                    }
                    else
                    {
                        if (info.PropertyType.ToString().Contains("Int") && int.Parse(value.ToString()) == 0)
                            continue;
                        if (info.PropertyType.ToString().Contains("Decimal") && decimal.Parse(value.ToString()) == 0)
                            continue;
                        sb.Append(name + "=" + value + " and ");
                    }
                }
            }
            string strSql = sb.ToString();
            if (strSql.Equals(""))
                return "";
            else
                return " Where (" + strSql.Substring(0, strSql.Length - 4) + ")";
        }
        internal List<T1> GetListData<T1>(string sql)
        {
            using (var conn = DataFactory.GetSQLConnection())
            {
                try
                {
                    var t = conn.Query<T1>(sql.ToString());
                    return t.AsList<T1>();
                }
                catch (Exception e)
                {
                    return null;
                }
            }
        }
    }
}
